﻿using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Threading;
using System.Threading.Tasks;

namespace Dapper.Sharding
{
    public abstract class IQuery
    {
        protected IDatabase db;
        internal int skip;
        internal int take;
        object par;
        DistributedTransaction tran;
        int? timeout;
        internal StringBuilder sqlTable = new();
        protected string returnFields;
        internal string sqlOrderBy;
        protected string sqlHaving;
        protected string sqlGroupBy;
        protected string sqlWhere;
        protected StringBuilder sbWhere;
        protected string distinct;
        internal string forUpdate;
        private CancellationToken token = default;

        public IQuery(IDatabase db)
        {
            this.db = db;
        }

        #region Abstract

        internal abstract void Init<T>(ITable<T> table, bool setField = true, bool setOrderby = true) where T : class;

        internal abstract void Init<T>(ITable<T> table, string asName, bool setField = true, bool setOrderby = true) where T : class;

        public abstract IQuery InnerJoin<T>(ITable<T> table, string asName, string on) where T : class;

        public abstract IQuery LeftJoin<T>(ITable<T> table, string asName, string on) where T : class;

        public abstract IQuery InnerJoin(string sql, string asName, string on);

        public abstract IQuery LeftJoin(string sql, string asName, string on);

        public abstract IQuery InnerJoin(string tableName, string on);

        public abstract IQuery LeftJoin(string tableName, string on);

        public abstract string Sql();

        public abstract string SqlCount();

        public abstract string SqlExists();

        public abstract string SqlMin(string field);

        public abstract string SqlMax(string field);

        public abstract string SqlSum(string field);

        public abstract string SqlAvg(string field);

        public string SqlSubQuery(string asName)
        {
            return $"({Sql()}) {asName}";
        }

        public string SqlSubQueryCount(string asName)
        {
            return $"({SqlCount()}) {asName}";
        }

        public string SqlSubQueryMin(string filed, string asName)
        {
            return $"({SqlMin(filed)}) {asName}";
        }

        public string SqlSubQueryMax(string filed, string asName)
        {
            return $"({SqlMax(filed)}) {asName}";
        }

        public string SqlSubQuerySum(string filed, string asName)
        {
            return $"({SqlSum(filed)}) {asName}";
        }

        public string SqlSubQueryAvg(string filed, string asName)
        {
            return $"({SqlAvg(filed)}) {asName}";
        }

        #endregion

        #region Method

        internal void InitSql(string sql, string asName)
        {
            sqlTable.Append('(');
            sqlTable.Append(sql);
            sqlTable.Append(')');
            sqlTable.Append(' ');
            sqlTable.Append(asName);
        }

        #endregion

        #region Query Condition

        public IQuery Where(string where)
        {
            if (string.IsNullOrEmpty(where))
            {
                sqlWhere = null;
            }
            else
            {
                sqlWhere = " WHERE " + where;
            }

            return this;
        }

        public IQuery WhereAnd(string and)
        {
            if (string.IsNullOrEmpty(and))
            {
                sbWhere?.Clear();
            }
            else
            {
                sbWhere ??= new();
                if (string.IsNullOrEmpty(sqlWhere))
                {
                    sqlWhere = " WHERE 1=1";
                }

                sbWhere.Append(" AND ");
                sbWhere.Append(and);
            }

            return this;
        }

        public IQuery WhereAndExists(IQuery q)
        {
            WhereAnd($"EXISTS({q.SqlExists()})");
            return this;
        }

        public IQuery WhereAndExists(string sql)
        {
            WhereAnd($"EXISTS({sql})");
            return this;
        }

        public IQuery GroupBy(string groupBy)
        {
            if (string.IsNullOrEmpty(groupBy))
            {
                sqlGroupBy = null;
            }
            else
            {
                sqlGroupBy = " GROUP BY " + groupBy;
            }

            return this;
        }

        public IQuery Having(string having)
        {
            if (string.IsNullOrEmpty(having))
            {
                sqlHaving = null;
            }
            else
            {
                sqlHaving = " HAVING " + having;
            }

            return this;
        }

        public IQuery OrderBy(string orderBy)
        {
            if (string.IsNullOrEmpty(orderBy))
            {
                sqlOrderBy = null;
            }
            else
            {
                sqlOrderBy = " ORDER BY " + orderBy;
            }

            return this;
        }

        public IQuery Distinct(bool set = true)
        {
            if (set)
            {
                distinct = " DISTINCT";
            }
            else
            {
                distinct = null;
            }

            return this;
        }

        public IQuery ForUpdate(bool noWait = false)
        {
            if (noWait)
            {
                forUpdate = " FOR UPDATE NOWAIT";
            }
            else
            {
                forUpdate = " FOR UPDATE";
            }
            return this;
        }

        public IQuery ReturnFields(string fields)
        {
            returnFields = fields;
            return this;
        }

        public IQuery Limit(int count)
        {
            skip = 0;
            take = count;
            return this;
        }

        public IQuery Limit(int skip, int take)
        {
            this.skip = skip;
            this.take = take;
            return this;
        }

        public IQuery Page(int page, int pageSize)
        {
            int skip = 0;
            if (page > 1)
            {
                skip = (page - 1) * pageSize;
            }
            this.skip = skip;
            take = pageSize;
            return this;
        }

        public IQuery Param(object param)
        {
            par = param;
            return this;
        }

        public IQuery Transaction(DistributedTransaction tran)
        {
            this.tran = tran;
            return this;
        }

        public IQuery CancellationToken(CancellationToken token)
        {
            this.token = token;
            return this;
        }

        public IQuery Timeout(int? timeout = null)
        {
            this.timeout = timeout;
            return this;
        }

        public void Clear()
        {
            sqlWhere = null;
            sbWhere?.Clear();
            sqlGroupBy = null;
            sqlHaving = null;
            sqlOrderBy = null;
            returnFields = null;
            distinct = null;
            forUpdate = null;
            skip = 0;
            take = 0;
        }

        #endregion

        #region Query

        public long Count()
        {
            return db.ExecuteScalar<long>(SqlCount(), par, tran, timeout);
        }

        public Task<long> CountAsync()
        {
            return db.ExecuteScalarAsync<long>(SqlCount(), par, tran, timeout, token);
        }

        public T QueryFirstOrDefault<T>()
        {
            return db.QueryFirstOrDefault<T>(Sql(), par, tran, timeout);
        }

        public Task<T> QueryFirstOrDefaultAsync<T>()
        {
            return db.QueryFirstOrDefaultAsync<T>(Sql(), par, tran, timeout, token);
        }

        public dynamic QueryFirstOrDefault()
        {
            return db.QueryFirstOrDefault(Sql(), par, tran, timeout);
        }

        public Task<dynamic> QueryFirstOrDefaultAsync()
        {
            return db.QueryFirstOrDefaultAsync(Sql(), par, tran, timeout, token);
        }

        public List<T> Query<T>()
        {
            return db.Query<T>(Sql(), par, tran, timeout);
        }

        public Task<List<T>> QueryAsync<T>()
        {
            return db.QueryAsync<T>(Sql(), par, tran, timeout, token);
        }

        public List<dynamic> Query()
        {
            return db.Query(Sql(), par, tran, timeout);
        }

        public Task<List<dynamic>> QueryAsync()
        {
            return db.QueryAsync(Sql(), par, tran, timeout, token);
        }

        public DataTable QueryDataTable()
        {
            return db.QueryDataTable(Sql(), par, tran, timeout);
        }

        public Task<DataTable> QueryDataTableAsync()
        {
            return db.QueryDataTableAsync(Sql(), par, tran, timeout, token);
        }

        public T ExecuteScalar<T>()
        {
            return db.ExecuteScalar<T>(Sql(), par, tran, timeout);
        }

        public Task<T> ExecuteScalarAsync<T>()
        {
            return db.ExecuteScalarAsync<T>(Sql(), par, tran, timeout, token);
        }

        public object ExecuteScalar()
        {
            return db.ExecuteScalar(Sql(), par, tran, timeout);
        }

        public Task<object> ExecuteScalarAsync()
        {
            return db.ExecuteScalarAsync(Sql(), par, tran, timeout, token);
        }

#if NET5_0_OR_GREATER

        public IAsyncEnumerable<T> QueryUnbufferedAsync<T>()
        {
            return db.QueryUnbufferedAsync<T>(Sql(), par, tran, timeout, token);
        }

        public IAsyncEnumerable<dynamic> QueryUnbufferedAsync()
        {
            return db.QueryUnbufferedAsync(Sql(), par, tran, timeout, token);
        }

#endif

        /**********QueryPageAndCount************/

        public PageEntity<T> QueryPageAndCount<T>()
        {
            return new PageEntity<T>
            {
                Data = Query<T>(),
                Count = Count()
            };
        }

        public async Task<PageEntity<T>> QueryPageAndCountAsync<T>()
        {
            var task1 = QueryAsync<T>();
            var task2 = CountAsync();
            await Task.WhenAll(task1, task2);
            return new PageEntity<T>
            {
                Data = task1.Result,
                Count = task2.Result
            };
        }

        public PageEntity<dynamic> QueryPageAndCount()
        {
            return new PageEntity<dynamic>
            {
                Data = Query(),
                Count = Count()
            };
        }

        public async Task<PageEntity<dynamic>> QueryPageAndCountAsync()
        {
            var task1 = QueryAsync();
            var task2 = CountAsync();
            await Task.WhenAll(task1, task2);
            return new PageEntity<dynamic>
            {
                Data = task1.Result,
                Count = task2.Result
            };
        }

#if NET5_0_OR_GREATER

        public async Task<PageEntityUnbuffered<T>> QueryPageAndCountUnbuffered<T>()
        {
            var count = await CountAsync();
            return new PageEntityUnbuffered<T>
            {
                Data = QueryUnbufferedAsync<T>(),
                Count = count
            };
        }

        public async Task<PageEntityUnbuffered<dynamic>> QueryPageAndCountUnbuffered()
        {
            var count = await CountAsync();
            return new PageEntityUnbuffered<dynamic>
            {
                Data = QueryUnbufferedAsync(),
                Count = count
            };
        }

#endif

        #endregion

        #region Union

        public IUnion Union(params IQuery[] querys)
        {
            var union = db.AsUnion();
            union.Union(this);
            foreach (var item in querys)
            {
                union.Union(item);
            }
            return union;
        }

        public IUnion Union(string sql)
        {
            var union = db.AsUnion(sql);
            union.Union(this);
            return union;
        }

        public IUnion UnionAll(params IQuery[] querys)
        {
            var union = db.AsUnion();
            union.UnionAll(this);
            foreach (var item in querys)
            {
                union.UnionAll(item);
            }
            return union;
        }

        public IUnion UnionAll(string sql)
        {
            var union = db.AsUnion(sql);
            union.UnionAll(this);
            return union;
        }

        #endregion

        #region aggregate

        public bool Exists()
        {
            return db.ExecuteScalar<bool>(SqlExists(), par, tran, timeout);
        }

        public async Task<bool> ExistsAsync()
        {
            return await db.ExecuteScalarAsync<bool>(SqlExists(), par, tran, timeout, token);
        }

        public TValue Min<TValue>(string field)
        {
            return db.ExecuteScalar<TValue>(SqlMin(field), par, tran, timeout);
        }

        public async Task<TValue> MinAsync<TValue>(string field)
        {
            return await db.ExecuteScalarAsync<TValue>(SqlMin(field), par, tran, timeout, token);
        }

        public TValue Max<TValue>(string field)
        {
            return db.ExecuteScalar<TValue>(SqlMax(field), par, tran, timeout);
        }

        public async Task<TValue> MaxAsync<TValue>(string field)
        {
            return await db.ExecuteScalarAsync<TValue>(SqlMax(field), par, tran, timeout, token);
        }

        public TValue Sum<TValue>(string field)
        {
            return db.ExecuteScalar<TValue>(SqlSum(field), par, tran, timeout);
        }

        public async Task<TValue> SumAsync<TValue>(string field)
        {
            return await db.ExecuteScalarAsync<TValue>(SqlSum(field), par, tran, timeout, token);
        }

        public decimal Avg(string field)
        {
            return db.ExecuteScalar<decimal>(SqlAvg(field), par, tran, timeout);
        }

        public async Task<decimal> AvgAsync(string field)
        {
            return await db.ExecuteScalarAsync<decimal>(SqlAvg(field), par, tran, timeout, token);
        }

        #endregion


    }
}
